package com.marvels.loanapply.api.impl;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import com.marvels.common.exception.MarvelsBusinessException;
import com.marvels.common.utils.MarvelsLogUtil;
import com.marvels.loanapply.api.LoanapplyCheckBillApi;
import com.marvels.loanapply.customer.dao.LoanCustomerDao;
import com.marvels.loanapply.dao.LoanapplyCheckBillDao;
import com.marvels.loanapply.dto.ChannelCostDTO;
import com.marvels.loanapply.dto.CheckBillDTO;
import com.marvels.loanapply.dto.FlowStatisticsDTO;

import cn.hutool.core.util.StrUtil;

@Service
public class LoanapplyCheckBillImpl implements LoanapplyCheckBillApi{

	@Autowired
	private LoanapplyCheckBillDao checkBillDao;
	@Autowired
	private LoanCustomerDao loanCustomerDao;

	@Override
	public List<FlowStatisticsDTO> selectFlowStatistics(CheckBillDTO dto) throws MarvelsBusinessException{
		PageHelper.startPage(dto.getPageNo(), dto.getPageSize());
		//List<FlowStatisticsDTO> list = checkBillDao.selectFlowStatistics(dto);
		// list = repairFlowStatisticsDataV2(list, dto);
		return checkBillDao.selectNewFlowStatistics(dto);
	}
	/**
	 * 完善数据值
	 * @param list
	 * @param dto
	 * @return
	 */
	public List<FlowStatisticsDTO> repairFlowStatisticsData(List<FlowStatisticsDTO> list, CheckBillDTO dto) throws MarvelsBusinessException{
		//初始化查询条件
		CheckBillDTO temDto = dto;
		DecimalFormat df=new DecimalFormat("0.00");
		//1.第一行新增统计汇总值
		if (list != null && list.size() == 1 && list.get(0).getChannelCode().equals("*")) {
			return new ArrayList<FlowStatisticsDTO>();
		}
		
		for (FlowStatisticsDTO flowStatisticsDTO : list) {
			// 后台总量
			if(StrUtil.equals(flowStatisticsDTO.getChannelCode(), "*")){
				
				temDto.setOrgChannelName(temDto.getChannelName());
				temDto.setChannelName(null);
				
				// 性能优化，单独提出来
				List<String> statisticsChannelNames = checkBillDao.selectFlowStatisticsChannelName(temDto);
				temDto.setStatisticsChannelNames(statisticsChannelNames);
				
				MarvelsLogUtil.getInstance().info("CheckBillImpl.repairFlowStatisticsData.query:" + JSONObject.toJSONString(temDto));
				// 获取该渠道贷款数
				Map<String, Object> loanCountMap = checkBillDao.selectLoadCount(temDto);
				flowStatisticsDTO.setAppFirstDownLoanCount(Integer.parseInt(loanCountMap.get("firstLoanCount").toString()));
				flowStatisticsDTO.setAppLoanCount(Integer.parseInt(loanCountMap.get("loanCount").toString()));
				
				//查询回款率
				Double returnSuccessRate = checkBillDao.queryReturnSuccessRate(temDto);
				flowStatisticsDTO.setReturnSuccessRate(df.format( returnSuccessRate)+"%");
				flowStatisticsDTO.setReturnSuccessRateReal(df.format( returnSuccessRate));
				//实名认证率   总实名数/总下载数
				if(flowStatisticsDTO.getTotleRegistrations()!=null || flowStatisticsDTO.getTotleAuthentication()!=null){
					if(flowStatisticsDTO.getTotleRegistrations() == 0){
						flowStatisticsDTO.setAuthenticationRate("0.00%");
					}else{
						double authenticationRate = (double)flowStatisticsDTO.getTotleAuthentication()/flowStatisticsDTO.getTotleRegistrations()*100;
						flowStatisticsDTO.setAuthenticationRate(df.format( authenticationRate)+"%");
					}
				}
				//app注册量
				if((flowStatisticsDTO.getTotleRegistrations() !=null && flowStatisticsDTO.getTotleRegistrations().intValue() !=0 ) && (flowStatisticsDTO.getAppDownLoadCount()!=null && flowStatisticsDTO.getAppDownLoadCount().intValue()!=0)){
					//app下载统计注册率   下载APP数AppDownLoadCount  / 总用户注册数TotleRegistrations
					double appRegistrationsRate = (double) flowStatisticsDTO.getAppDownLoadCount()/flowStatisticsDTO.getTotleRegistrations()*100;
					flowStatisticsDTO.setAppRegistrationsRate("("+df.format(appRegistrationsRate)+"%)");
				}else{
					flowStatisticsDTO.setAppRegistrationsRate("(0.00%)");
				}
				//先初始化好
				flowStatisticsDTO.setAppLoanRate("(0.00%)");
				flowStatisticsDTO.setAppLoanRateReal("0.00");
				flowStatisticsDTO.setAppFirstDownLoanRate("(0.00%)");
				if(flowStatisticsDTO.getAppLoanCount() != null && flowStatisticsDTO.getH5Registrations() != null){
					double appLoanRate = 0D;
					if("*".equals(flowStatisticsDTO.getChannelCode()) && (flowStatisticsDTO.getH5Registrations() + flowStatisticsDTO.getTemp()) != 0){
						//后台总量特殊处理
						appLoanRate = (double)flowStatisticsDTO.getAppLoanCount() / (flowStatisticsDTO.getH5Registrations() + flowStatisticsDTO.getTemp()) * 100;
					}
					flowStatisticsDTO.setAppLoanRateReal(df.format(appLoanRate));
					flowStatisticsDTO.setAppLoanRate("("+df.format(appLoanRate)+"%)");
				}
				
				if(flowStatisticsDTO.getAppFirstDownLoanCount() != null && flowStatisticsDTO.getH5Registrations() != null){
					double appFirstDownLoanRate = 0D;
					if("*".equals(flowStatisticsDTO.getChannelCode()) && (flowStatisticsDTO.getH5Registrations() + flowStatisticsDTO.getTemp()) != 0){
						//后台总量特殊处理
						appFirstDownLoanRate = (double)flowStatisticsDTO.getAppFirstDownLoanCount() / (flowStatisticsDTO.getH5Registrations() + flowStatisticsDTO.getTemp()) * 100;
					}
					flowStatisticsDTO.setAppFirstDownLoanRate("("+df.format(appFirstDownLoanRate)+"%)");
				}
				Double repeatLoanRate=checkBillDao.queryRepeatLoanRate(temDto);
				flowStatisticsDTO.setRepeatLoanRate(df.format(repeatLoanRate==null?0.00:repeatLoanRate*100));
			}else{
				temDto.setOrgChannelName(temDto.getChannelName());
				//正常数据
				if(StringUtils.isBlank(flowStatisticsDTO.getChannelCode())){
					//统计自然流量
					temDto.setChannelName("");
				}else{
					temDto.setChannelName(flowStatisticsDTO.getChannelName());
				}
				
				// 性能优化，单独提出来
				List<String> statisticsChannelNames = checkBillDao.selectFlowStatisticsChannelName(temDto);
				temDto.setStatisticsChannelNames(statisticsChannelNames);
				
				MarvelsLogUtil.getInstance().info("CheckBillImpl.repairFlowStatisticsData.query:" + JSONObject.toJSONString(temDto));
				// 获取该渠道贷款数
				Map<String, Object> loanCountMap = checkBillDao.selectLoadCount(temDto);
				flowStatisticsDTO.setAppFirstDownLoanCount(Integer.parseInt(loanCountMap.get("firstLoanCount").toString()));
				flowStatisticsDTO.setAppLoanCount(Integer.parseInt(loanCountMap.get("loanCount").toString()));
				
				//查询回款率
				Double returnSuccessRate = checkBillDao.queryReturnSuccessRate(temDto);
				flowStatisticsDTO.setReturnSuccessRateReal(df.format( returnSuccessRate));
				flowStatisticsDTO.setReturnSuccessRate(df.format( returnSuccessRate)+"%");
				
				//实名认证率   总实名数/总下载数
				if(flowStatisticsDTO.getTotleRegistrations()!=null || flowStatisticsDTO.getTotleAuthentication()!=null){
					if(flowStatisticsDTO.getTotleRegistrations() == 0){
						flowStatisticsDTO.setAuthenticationRate("0.00%");
					}else{
						double authenticationRate = (double)flowStatisticsDTO.getTotleAuthentication()/flowStatisticsDTO.getTotleRegistrations()*100;
						flowStatisticsDTO.setAuthenticationRate(df.format( authenticationRate)+"%");
					}
				}
				//app注册量
				if((flowStatisticsDTO.getTotleRegistrations() !=null && flowStatisticsDTO.getTotleRegistrations().intValue() !=0 ) && (flowStatisticsDTO.getAppDownLoadCount()!=null && flowStatisticsDTO.getAppDownLoadCount().intValue()!=0)){
					//app下载统计注册率   下载APP数AppDownLoadCount  / 总用户注册数TotleRegistrations
					double appRegistrationsRate = (double) flowStatisticsDTO.getAppDownLoadCount()/flowStatisticsDTO.getTotleRegistrations()*100;
					flowStatisticsDTO.setAppRegistrationsRate("("+df.format(appRegistrationsRate)+"%)");
				}else{
					flowStatisticsDTO.setAppRegistrationsRate("(0.00%)");
				}
				//先初始化好
				flowStatisticsDTO.setAppLoanRate("(0.00%)");
				flowStatisticsDTO.setAppLoanRateReal("0.00");
				flowStatisticsDTO.setAppFirstDownLoanRate("(0.00%)");
				if(flowStatisticsDTO.getAppLoanCount() != null && flowStatisticsDTO.getH5Registrations() != null){
					double appLoanRate = 0D;
					if(StringUtils.isBlank(flowStatisticsDTO.getChannelCode()) && (flowStatisticsDTO.getH5Registrations() + flowStatisticsDTO.getAppDownLoadCount()) != 0){
						//自然流量特殊处理
						appLoanRate = (double)flowStatisticsDTO.getAppLoanCount() / (flowStatisticsDTO.getH5Registrations() + flowStatisticsDTO.getAppDownLoadCount()) * 100;
					}else if(flowStatisticsDTO.getH5Registrations() != 0){
						appLoanRate = (double)flowStatisticsDTO.getAppLoanCount() / flowStatisticsDTO.getH5Registrations() * 100;
					}
					flowStatisticsDTO.setAppLoanRate("("+df.format(appLoanRate)+"%)");
					flowStatisticsDTO.setAppLoanRateReal(df.format(appLoanRate));
				}
				
				if(flowStatisticsDTO.getAppFirstDownLoanCount() != null && flowStatisticsDTO.getH5Registrations() != null){
					double appFirstDownLoanRate = 0D;
					if(StringUtils.isBlank(flowStatisticsDTO.getChannelCode()) && (flowStatisticsDTO.getH5Registrations() + flowStatisticsDTO.getAppDownLoadCount()) != 0){
						//自然流量特殊处理
						appFirstDownLoanRate = (double)flowStatisticsDTO.getAppFirstDownLoanCount() / (flowStatisticsDTO.getH5Registrations() + flowStatisticsDTO.getAppDownLoadCount()) * 100;
					}else if(flowStatisticsDTO.getH5Registrations() != 0){
						appFirstDownLoanRate = (double)flowStatisticsDTO.getAppFirstDownLoanCount() / flowStatisticsDTO.getH5Registrations() * 100;
					}
					flowStatisticsDTO.setAppFirstDownLoanRate("("+df.format(appFirstDownLoanRate)+"%)");
				}
				Double repeatLoanRate=checkBillDao.queryRepeatLoanRate(temDto);
				flowStatisticsDTO.setRepeatLoanRate(df.format(repeatLoanRate==null?0.00:repeatLoanRate*100));
			}
		}
		return list;
	}
	
	/**
	 * 所有取数都是跟随该段时间内的注册用户
	 * @param list
	 * @param dto
	 * @return
	 * @throws MarvelsBusinessException
	 */
	public List<FlowStatisticsDTO> repairFlowStatisticsDataV2(List<FlowStatisticsDTO> list, CheckBillDTO dto) throws MarvelsBusinessException{
		//初始化查询条件
		CheckBillDTO temDto = dto;
		DecimalFormat df=new DecimalFormat("0.00");
		//1.第一行新增统计汇总值
		if (list != null && list.size() == 1 && list.get(0).getChannelCode().equals("*")) {
			return new ArrayList<FlowStatisticsDTO>();
		}
		
		for (FlowStatisticsDTO flowStatisticsDTO : list) {
			// 后台总量
			if(StrUtil.equals(flowStatisticsDTO.getChannelCode(), "*")){
				
				temDto.setOrgChannelName(temDto.getChannelName());
				temDto.setChannelName(null);
				
				// 性能优化，单独提出来
				List<String> statisticsChannelNames = checkBillDao.selectFlowStatisticsChannelName(temDto);
				temDto.setStatisticsChannelNames(statisticsChannelNames);
				
				MarvelsLogUtil.getInstance().info("CheckBillImpl.repairFlowStatisticsData.query:" + JSONObject.toJSONString(temDto));
				// 获取该渠道贷款数
				Map<String, Object> loanCountMap = checkBillDao.selectLoadCountV2(temDto);
				flowStatisticsDTO.setAppFirstDownLoanCount(Integer.parseInt(loanCountMap.get("firstLoanCount").toString()));
				flowStatisticsDTO.setAppLoanCount(Integer.parseInt(loanCountMap.get("loanCount").toString()));
				// 复贷率  复贷笔数/总贷款笔数
				if(flowStatisticsDTO.getAppLoanCount() == 0){
					flowStatisticsDTO.setRepeatLoanRate(df.format(0.00));
				}else{
					Double repeatLoanRate = Double.parseDouble(loanCountMap.get("reLoanCount").toString()) / (double)flowStatisticsDTO.getAppLoanCount();
					flowStatisticsDTO.setRepeatLoanRate(df.format(repeatLoanRate*100));
				}
				
				//查询回款率
				Double returnSuccessRate = checkBillDao.queryReturnSuccessRateV2(temDto);
				flowStatisticsDTO.setReturnSuccessRate(df.format( returnSuccessRate)+"%");
				flowStatisticsDTO.setReturnSuccessRateReal(df.format( returnSuccessRate));
				//实名认证率   总实名数/总下载数
				if(flowStatisticsDTO.getTotleRegistrations()!=null || flowStatisticsDTO.getTotleAuthentication()!=null){
					if(flowStatisticsDTO.getTotleRegistrations() == 0){
						flowStatisticsDTO.setAuthenticationRate("0.00%");
					}else{
						double authenticationRate = (double)flowStatisticsDTO.getTotleAuthentication()/flowStatisticsDTO.getTotleRegistrations()*100;
						flowStatisticsDTO.setAuthenticationRate(df.format( authenticationRate)+"%");
					}
				}
				//app注册量
				if((flowStatisticsDTO.getTotleRegistrations() !=null && flowStatisticsDTO.getTotleRegistrations().intValue() !=0 ) && (flowStatisticsDTO.getAppDownLoadCount()!=null && flowStatisticsDTO.getAppDownLoadCount().intValue()!=0)){
					//app下载统计注册率   下载APP数AppDownLoadCount  / 总用户注册数TotleRegistrations
					double appRegistrationsRate = (double) flowStatisticsDTO.getAppDownLoadCount()/flowStatisticsDTO.getTotleRegistrations()*100;
					flowStatisticsDTO.setAppRegistrationsRate("("+df.format(appRegistrationsRate)+"%)");
				}else{
					flowStatisticsDTO.setAppRegistrationsRate("(0.00%)");
				}
				//先初始化好
				flowStatisticsDTO.setAppLoanRate("(0.00%)");
				flowStatisticsDTO.setAppLoanRateReal("0.00");
				flowStatisticsDTO.setAppFirstDownLoanRate("(0.00%)");
				if(flowStatisticsDTO.getAppLoanCount() != null && flowStatisticsDTO.getH5Registrations() != null){
					double appLoanRate = 0D;
					if("*".equals(flowStatisticsDTO.getChannelCode()) && (flowStatisticsDTO.getH5Registrations()/* + flowStatisticsDTO.getTemp()*/) != 0){
						//后台总量特殊处理
						appLoanRate = (double)flowStatisticsDTO.getAppLoanCount() / (flowStatisticsDTO.getH5Registrations()/* + flowStatisticsDTO.getTemp()*/) * 100;
					}
					flowStatisticsDTO.setAppLoanRateReal(df.format(appLoanRate));
					flowStatisticsDTO.setAppLoanRate("("+df.format(appLoanRate)+"%)");
				}
				
				if(flowStatisticsDTO.getAppFirstDownLoanCount() != null && flowStatisticsDTO.getH5Registrations() != null){
					double appFirstDownLoanRate = 0D;
					if("*".equals(flowStatisticsDTO.getChannelCode()) && (flowStatisticsDTO.getH5Registrations()/* + flowStatisticsDTO.getTemp()*/) != 0){
						//后台总量特殊处理
						appFirstDownLoanRate = (double)flowStatisticsDTO.getAppFirstDownLoanCount() / (flowStatisticsDTO.getH5Registrations()/* + flowStatisticsDTO.getTemp()*/) * 100;
					}
					flowStatisticsDTO.setAppFirstDownLoanRate("("+df.format(appFirstDownLoanRate)+"%)");
				}
			}else{
				temDto.setOrgChannelName(temDto.getChannelName());
				//正常数据
				if(StringUtils.isBlank(flowStatisticsDTO.getChannelCode())){
					//统计自然流量
					temDto.setChannelName("");
				}else{
					temDto.setChannelName(flowStatisticsDTO.getChannelName());
				}
				
				// 性能优化，单独提出来
				List<String> statisticsChannelNames = checkBillDao.selectFlowStatisticsChannelName(temDto);
				temDto.setStatisticsChannelNames(statisticsChannelNames);
				
				MarvelsLogUtil.getInstance().info("CheckBillImpl.repairFlowStatisticsData.query:" + JSONObject.toJSONString(temDto));
				// 获取该渠道贷款数
				Map<String, Object> loanCountMap = checkBillDao.selectLoadCountV2(temDto);
				flowStatisticsDTO.setAppFirstDownLoanCount(Integer.parseInt(loanCountMap.get("firstLoanCount").toString()));
				flowStatisticsDTO.setAppLoanCount(Integer.parseInt(loanCountMap.get("loanCount").toString()));
				// 复贷率  复贷笔数/总贷款笔数
				if(flowStatisticsDTO.getAppLoanCount() == 0){
					flowStatisticsDTO.setRepeatLoanRate(df.format(0.00));
				}else{
					Double repeatLoanRate = Double.parseDouble(loanCountMap.get("reLoanCount").toString()) / (double)flowStatisticsDTO.getAppLoanCount();
					flowStatisticsDTO.setRepeatLoanRate(df.format(repeatLoanRate*100));
				}
				//查询回款率
				Double returnSuccessRate = checkBillDao.queryReturnSuccessRateV2(temDto);
				flowStatisticsDTO.setReturnSuccessRateReal(df.format( returnSuccessRate));
				flowStatisticsDTO.setReturnSuccessRate(df.format( returnSuccessRate)+"%");
				
				//实名认证率   总实名数/总下载数
				if(flowStatisticsDTO.getTotleRegistrations()!=null || flowStatisticsDTO.getTotleAuthentication()!=null){
					if(flowStatisticsDTO.getTotleRegistrations() == 0){
						flowStatisticsDTO.setAuthenticationRate("0.00%");
					}else{
						double authenticationRate = (double)flowStatisticsDTO.getTotleAuthentication()/flowStatisticsDTO.getTotleRegistrations()*100;
						flowStatisticsDTO.setAuthenticationRate(df.format( authenticationRate)+"%");
					}
				}
				//app注册量
				if((flowStatisticsDTO.getTotleRegistrations() !=null && flowStatisticsDTO.getTotleRegistrations().intValue() !=0 ) && (flowStatisticsDTO.getAppDownLoadCount()!=null && flowStatisticsDTO.getAppDownLoadCount().intValue()!=0)){
					//app下载统计注册率   下载APP数AppDownLoadCount  / 总用户注册数TotleRegistrations
					double appRegistrationsRate = (double) flowStatisticsDTO.getAppDownLoadCount()/flowStatisticsDTO.getTotleRegistrations()*100;
					flowStatisticsDTO.setAppRegistrationsRate("("+df.format(appRegistrationsRate)+"%)");
				}else{
					flowStatisticsDTO.setAppRegistrationsRate("(0.00%)");
				}
				//先初始化好
				flowStatisticsDTO.setAppLoanRate("(0.00%)");
				flowStatisticsDTO.setAppLoanRateReal("0.00");
				flowStatisticsDTO.setAppFirstDownLoanRate("(0.00%)");
				if(flowStatisticsDTO.getAppLoanCount() != null && flowStatisticsDTO.getH5Registrations() != null){
					double appLoanRate = 0D;
					if(StringUtils.isBlank(flowStatisticsDTO.getChannelCode()) && (flowStatisticsDTO.getH5Registrations() + flowStatisticsDTO.getAppDownLoadCount()) != 0){
						//自然流量特殊处理
						appLoanRate = (double)flowStatisticsDTO.getAppLoanCount() / (flowStatisticsDTO.getH5Registrations() + flowStatisticsDTO.getAppDownLoadCount()) * 100;
					}else if(flowStatisticsDTO.getH5Registrations() != 0){
						appLoanRate = (double)flowStatisticsDTO.getAppLoanCount() / flowStatisticsDTO.getH5Registrations() * 100;
					}
					flowStatisticsDTO.setAppLoanRate("("+df.format(appLoanRate)+"%)");
					flowStatisticsDTO.setAppLoanRateReal(df.format(appLoanRate));
				}
				
				if(flowStatisticsDTO.getAppFirstDownLoanCount() != null && flowStatisticsDTO.getH5Registrations() != null){
					double appFirstDownLoanRate = 0D;
					if(StringUtils.isBlank(flowStatisticsDTO.getChannelCode()) && (flowStatisticsDTO.getH5Registrations() + flowStatisticsDTO.getAppDownLoadCount()) != 0){
						//自然流量特殊处理
						appFirstDownLoanRate = (double)flowStatisticsDTO.getAppFirstDownLoanCount() / (flowStatisticsDTO.getH5Registrations() + flowStatisticsDTO.getAppDownLoadCount()) * 100;
					}else if(flowStatisticsDTO.getH5Registrations() != 0){
						appFirstDownLoanRate = (double)flowStatisticsDTO.getAppFirstDownLoanCount() / flowStatisticsDTO.getH5Registrations() * 100;
					}
					flowStatisticsDTO.setAppFirstDownLoanRate("("+df.format(appFirstDownLoanRate)+"%)");
				}
			}
		}
		return list;
	}
	
	@SuppressWarnings("deprecation")
	public HSSFWorkbook findFlowStatisticsExcelWorkbook(CheckBillDTO dto) throws MarvelsBusinessException {
		HSSFWorkbook wb = new HSSFWorkbook();
		List<FlowStatisticsDTO> reminderList = checkBillDao.selectFlowStatistics(dto);
		// reminderList = repairFlowStatisticsDataV2(reminderList, dto);
		HSSFSheet sheet = wb.createSheet("流量统计");
		HSSFRow row = sheet.createRow(0);
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
		HSSFCell cell = row.createCell((short) 0);
		cell.setCellValue("序号");
		cell.setCellStyle(style);
		cell = row.createCell((short) 1);
		cell.setCellValue("平台");
		cell.setCellStyle(style);
		cell = row.createCell((short) 2);
		cell.setCellValue("实名数量");
		cell.setCellStyle(style);
		cell = row.createCell((short) 3);
		cell.setCellValue("实名率");
		cell.setCellStyle(style);
		cell = row.createCell((short) 4);
		cell.setCellValue("H5注册量");
		cell.setCellStyle(style);
		cell = row.createCell((short) 5);
		cell.setCellValue("APP下载注册量");
		cell.setCellStyle(style);
		cell = row.createCell((short) 6);
		cell.setCellValue("APP下款统计(首次)");
		cell.setCellStyle(style);
		cell = row.createCell((short) 7);
		cell.setCellValue("APP下款统计");
		cell.setCellStyle(style);
		cell = row.createCell((short) 8);
		cell.setCellValue("回款率");
		cell.setCellStyle(style);
		//写入实体数据 实际应用中这些数据从数据库得到，
		for (int i = 0; i < reminderList.size(); i++) {
			FlowStatisticsDTO data = reminderList.get(i);
			row = sheet.createRow((int) i + 1);
			// 创建单元格，并设置值
			cell = row.createCell((short) 0);
			cell.setCellValue(i + 1);
			cell = row.createCell((short) 1);
			if(org.apache.commons.lang.StringUtils.isNotBlank(data.getChannelName())){				
				cell.setCellValue(data.getChannelName()+"");						
			}else{
				cell.setCellValue("自然流量");
			}
		    cell = row.createCell((short) 2);
			cell.setCellValue((data.getTotleAuthentication()==null?0:data.getTotleAuthentication())+"");
			cell = row.createCell((short) 3);
			cell.setCellValue(data.getAuthenticationRate());
			
			cell = row.createCell((short) 4);
			cell.setCellValue((data.getH5Registrations()==null?0:data.getH5Registrations()));
			cell = row.createCell((short) 5);
			cell.setCellValue( data.getAppDownLoadCount() + data.getAppRegistrationsRate());//app下载量
			cell = row.createCell((short) 6);
			cell.setCellValue((data.getAppFirstDownLoanCount()==null?0 + data.getAppFirstDownLoanRate():data.getAppFirstDownLoanCount() + data.getAppFirstDownLoanRate()));
			cell = row.createCell((short) 7);
			cell.setCellValue((data.getAppLoanCount()==null?0 + data.getAppLoanRate():data.getAppLoanCount() + data.getAppLoanRate()));						

			cell = row.createCell((short) 8);
			cell.setCellValue(data.getReturnSuccessRate());
		}
		return wb;
	}
	
	@Override
	public String selectUserIdByName(String userName){
		if (StringUtils.isNotBlank(userName.trim())) {			
			return checkBillDao.selectUserIdByName(userName);
		}
		return null;
	}
	@Override
	public String selectUserNameByPhone(String mobile){
		if (StringUtils.isNotBlank(mobile)) {
			String userName = checkBillDao.selectUserNameByPhone(mobile);	
			if (StringUtils.isNotBlank(userName)) {
				return userName;
			}
			return "无";
		}
		return "无";
	}
	
	@Override
	public Map<String,String> selectUserNameByPhone1(String mobileStr){
		if (StringUtils.isNotBlank(mobileStr)) {
			String [] mobile = mobileStr.split(",");
			StringBuffer sb = new StringBuffer();
			String mobiles = null;
			for(int i=0;i<mobile.length;i++){
				sb.append("'"+mobile[i].replaceAll(" ","").trim()+"',");
			}
			if (sb.length()>1) {
				mobiles = sb.substring(0, sb.length()-1);	
			}
			List<Map<String,String>> userNameList = checkBillDao.selectUserNameByPhone1(mobiles);	
			Map<String,String> resultMap = new HashMap<String,String>();
			for(int i=0;i<userNameList.size();i++){
				Map<String,String> item = userNameList.get(i);
				resultMap.put(item.get("mobile"), item.get("userName"));
			}
			
			return resultMap;
		}
		return null;
	}
	
	/**
	 * 获取该时间段内指定续贷用户
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Override
	public String selectContinueLoanUser(String startDate,String endDate){
		List<String> icCardList = checkBillDao.selectContinueLoanUser(startDate,endDate);
		String icCardStr = StringUtils.join(icCardList, ",");
		return icCardStr;
	}
	
	/**
	 * 将财务的放款时间同步到ac_loan_apply_info表中
	 * @param loanId
	 * @return
	 */
	@Override
	public String synchroMoneyHandOutTime(String loanId){
		MarvelsLogUtil.getInstance().info("将财务的放款时间同步到ac_loan_apply_info表中:loanId="+loanId);
		checkBillDao.synchroMoneyHandOutTime(loanId);
		String reloanFlag = loanCustomerDao.getReloanFlagByLoanId(loanId);
		
		return reloanFlag;
	}
	
	
	@Override
	public List<ChannelCostDTO> channelCost(CheckBillDTO dto) throws MarvelsBusinessException {
		return checkBillDao.queryChannelCost(dto);
	}
	@Override
	public HSSFWorkbook channelCostDown(CheckBillDTO dto) throws MarvelsBusinessException {
		HSSFWorkbook wb = new HSSFWorkbook();
		List<ChannelCostDTO> reminderList = channelCost(dto);
		HSSFSheet sheet = wb.createSheet("渠道成本");
		HSSFRow row = sheet.createRow(0);
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
		HSSFCell cell = row.createCell((short) 0);
		cell.setCellValue("渠道名称");
		cell.setCellStyle(style);
		cell = row.createCell((short) 1);
		cell.setCellValue("注册数");
		cell.setCellStyle(style);
		cell = row.createCell((short) 2);
		cell.setCellValue("进件数");
		cell.setCellStyle(style);
		cell = row.createCell((short) 3);
		cell.setCellValue("成功放款");
		cell.setCellStyle(style);
		cell = row.createCell((short) 4);
		cell.setCellValue("放款金额");
		cell.setCellStyle(style);
		cell = row.createCell((short) 5);
		cell.setCellValue("cpa");
		cell.setCellStyle(style);
		cell = row.createCell((short) 6);
		cell.setCellValue("预估成本");
		cell.setCellStyle(style);
		//写入实体数据 实际应用中这些数据从数据库得到，
		for (int i = 0; i < reminderList.size(); i++) {
			ChannelCostDTO data = reminderList.get(i);
			row = sheet.createRow((int) i + 1);
			// 创建单元格，并设置值
			cell = row.createCell((short) 0);
			cell.setCellValue(data.getChannelName());
			cell = row.createCell((short) 1);
			cell.setCellValue(data.getRegisterCount());		
		    cell = row.createCell((short) 2);
			cell.setCellValue(data.getEntriesNumber());
			cell = row.createCell((short) 3);
			cell.setCellValue(data.getLoanCount());
			cell = row.createCell((short) 4);
			cell.setCellValue(data.getLoanAmount());
			cell = row.createCell((short) 5);
			cell.setCellValue(data.getCpa());
			cell = row.createCell((short) 6);
			cell.setCellValue(data.getEstimatedCost());
		}
		return wb;
	}
		
}
